import sqlite3
import csv
import os
import chardet
from typing import Optional

def detect_file_encoding(file_path: str) -> str:
    """自动检测文件编码"""
    with open(file_path, 'rb') as f:
        rawdata = f.read(10000)  # 读取前10000字节用于检测编码
        result = chardet.detect(rawdata)
        return result['encoding'] or 'utf-8'  # 默认使用utf-8

def create_database(db_path: str, csv_path: str, table_name: str) -> Optional[str]:
    """从CSV文件导入数据到SQLite，自动覆盖已存在的表"""
    try:
        # 检查CSV文件是否存在
        if not os.path.exists(csv_path):
            return f"错误：CSV文件 {csv_path} 不存在"

        # 检测文件编码
        try:
            encoding = detect_file_encoding(csv_path)
            print(f"检测到文件编码: {encoding}")
        except Exception as e:
            encoding = 'utf-8'  # 如果检测失败则默认UTF-8
            print(f"编码检测失败，使用默认UTF-8: {str(e)}")

        # 连接数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 读取CSV并处理
        with open(csv_path, 'r', encoding=encoding) as f:
            reader = csv.reader(f)
            
            # 获取列头（自动去除空格）
            headers = [header.strip() for header in next(reader)]
            
            # 删除已存在的表（自动覆盖）
            cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
            
            # 创建新表（所有字段设为TEXT类型）
            create_sql = f"""
            CREATE TABLE {table_name} (
                {", ".join([f'"{header}" TEXT' for header in headers])}
            )
            """
            cursor.execute(create_sql)
            
            # 批量插入数据
            insert_sql = f"""
            INSERT INTO {table_name} 
            VALUES ({", ".join(["?"] * len(headers))})
            """
            cursor.executemany(insert_sql, reader)
        
        conn.commit()
        return None

    except UnicodeDecodeError:
        # 如果默认编码失败，尝试GBK编码（常见中文编码）
        try:
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()
            
            with open(csv_path, 'r', encoding='gbk') as f:
                reader = csv.reader(f)
                headers = [header.strip() for header in next(reader)]
                
                cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
                cursor.execute(f"""
                    CREATE TABLE {table_name} (
                        {", ".join([f'"{header}" TEXT' for header in headers])}
                    )
                """)
                
                cursor.executemany(
                    f"INSERT INTO {table_name} VALUES ({', '.join(['?']*len(headers))})",
                    reader
                )
            
            conn.commit()
            return None
            
        except Exception as e:
            return f"GBK编码尝试失败: {str(e)}"
            
    except Exception as e:
        return f"发生错误: {str(e)}"
    finally:
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    # 配置参数
    db_path = "asa.db"    # 数据库路径
    csv_path = "asa.csv"  # CSV文件路径
    table_name = "asa"    # 表名
    
    # 执行导入建筑模块清单
    error = create_database(db_path, csv_path, table_name)
    
    if error:
        print(f"错误: {error}")
    else:
        print(f"数据已成功导入 {table_name} 表（自动覆盖模式）")
    # 执行导入所需材料清单
    csv_path = "need.csv"  # CSV文件路径
    table_name = "need"    # 表名
    error = create_database(db_path, csv_path, table_name)
    
    if error:
        print(f"错误: {error}")
    else:
        print(f"数据已成功导入 {table_name} 表（自动覆盖模式）")

    